﻿using QG.FrontDesk.Core;
using QG.FrontDesk.DAL.IRepository;
using QG.FrontDesk.Model;
using System.Collections.Generic;

namespace QG.FrontDesk.DAL.Repository
{
    public class PositionSubscRepository : IPositionSubscRepository
    {
        /// <summary>
        /// 职位订阅——获取所有职位名称
        /// </summary>
        /// <returns></returns>
        public List<Classifyinfo> GetClassify()
        {
            string sql = "SELECT * FROM classifyInfo";
            return DapperDBHelper<Classifyinfo>.QueryList(sql, null);
        }

        /// <summary>
        /// 职位订阅——获取所有职位名称
        /// </summary>
        /// <returns></returns>
        public List<PostName> GetPostName()
        {
            string sql = "SELECT * FROM postname";
            return DapperDBHelper<PostName>.QueryList(sql, null);
        }


        /// <summary>
        /// 职位订阅——新增一条职位订阅记录
        /// </summary>
        /// <param name="positionSubscribe"></param>
        /// <returns></returns>
        public bool AddPositionSubscribe(PositionSubscribe positionSubscribe)
        {
            string sql = "INSERT INTO positionsubscribe " +
                 $"VALUE(@SubscId, @Email, @SendCycle, @PostNameId, @CityId, @PhaseId, @IndustryId, @SalaryRange,@Time)";
            return DapperDBHelper<PositionSubscribe>.Execute(sql, positionSubscribe) > 0;
        }

        /// <summary>
        /// 新增职位
        /// </summary>
        /// <param name="positionInfo"></param>
        /// <param name="postName"></param>
        /// <returns></returns>
        public int AddPosition(PositionInfo positionInfo, PostName postName)
        {
            //如果是一个全新的职位,则插入数据库表
            string sql = @"INSERT INTO postName(NAME,classId,statu) 
                            SELECT @NAME,@ClassId,1 FROM DUAL
                            WHERE NOT EXISTS(SELECT NAME, classId, statu FROM postName WHERE NAME = @NAME)";
            DapperDBHelper<PostName>.Execute(sql, postName);
            //得到对应的Id
            sql = "SELECT  postNameId FROM postName WHERE Name=@Name";
            positionInfo.PostNameId = DapperDBHelper<PostName>.Query(sql, postName);
            //插入职位数据
            sql = @"INSERT INTO positionInfo(`compId`,`postNameId`,`department`,`workNature`,`minSalary`,`maxSalary`,`cityId`,`workExp`,`education`,`tempt`,postremark,`workAddress`,`statu`,`PublishTime`)
    VALUES(@compId, @postNameId, @department, @workNature, @minSalary, @maxSalary, @cityId, @workExp, @education, @tempt,@postremark,workAddress, @statu,@PublishTime)";
            return DapperDBHelper<PositionInfo>.Execute(sql, positionInfo);
        }


        /// <summary>
        /// 得到对应职位信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public PositionInfo GetPositionInfo(int Id, int UserId)
        {
            string sql = @"SELECT * FROM positioninfo WHERE PostId=@PostId";
            PositionInfo positionInfo = DapperDBHelper<PositionInfo>.QueryFirstOrDefault(sql, new { PostId = Id });
            //得到城市信息
            sql = "select * from cityinfo where cityId=@cityId";
            positionInfo.Cityinfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { cityId = positionInfo.CityId });
            //职业名称
            sql = "SELECT * FROM postName WHERE postNameId=@postNameId";
            positionInfo.Postname = DapperDBHelper<PostName>.QueryFirstOrDefault(sql, new { postNameId = positionInfo.PostNameId });
            //收藏表
            sql = "SELECT * FROM collectPost WHERE PostId=@PostId AND UserId=@UserId";
            positionInfo.Collectpost = DapperDBHelper<CollectPost>.QueryList(sql, new { PostId = Id, UserId = UserId });
            return positionInfo;
        }

        /// <summary>
        /// 收藏职位——新增一条收藏职位表记录
        /// </summary>
        /// <param name="collectPost">收藏职位表</param>
        /// <returns></returns>
        public bool AddCollection(CollectPost collectPost)
        {
            string sql = @"INSERT INTO collectPost(`userId`,`postId`) VALUE (@UserId, @PostId)";
            return DapperDBHelper<PositionSubscribe>.Execute(sql, collectPost) > 0;
        }

        /// <summary>
        /// 收藏职位——取消收藏
        /// </summary>
        /// <param name="collectPost"></param>
        /// <returns></returns>
        public bool DelCollection(CollectPost collectPost)
        {
            string sql = "DELETE FROM collectPost WHERE userId=@userId and postId=@postId";
            return DapperDBHelper<PositionSubscribe>.Execute(sql, new { userId = collectPost.UserId, postId = collectPost.PostId }) > 0;
        }


        /// <summary>
        /// 收藏职位——展示收藏职位信息
        /// </summary>
        /// <param name="cpId"></param>
        /// <returns></returns>
        public List<CollectPost> GetCollectPost(int userId)
        {
            //简历信息表
            string sql = @"SELECT * FROM collectPost WHERE userId = @userId";

            List<CollectPost> collectPost = DapperDBHelper<CollectPost>.QueryList(sql, new { userId = userId });

            foreach (var item in collectPost)
            {
                //获取对应的职位信息表信息
                sql = @"SELECT * FROM collectPost
                    INNER JOIN positionInfo ON collectPost.`postId` = positionInfo.`postId`
                    INNER JOIN cityinfo ON positionInfo.cityId = cityinfo.`cityId`
                    INNER JOIN company ON positionInfo.`compId` = company.`compId`
                    INNER JOIN industry ON company.`industryId` = industry.`industryId`
                    INNER JOIN developmentphase ON company.`phaseId` = developmentphase.`phaseId`
                    INNER JOIN postname ON postname.`postNameId`=positioninfo.`postNameId`
                    WHERE cpId = @CpId";
                item.Positioninfo = DapperDBHelper<PositionInfo>.QueryFirstOrDefault(sql, new { CpId = item.CpId });
                item.Positioninfo.Company = DapperDBHelper<Company>.QueryFirstOrDefault(sql, new { CpId = item.CpId });
                item.Positioninfo.Company.CityInfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { CpId = item.CpId });
                item.Positioninfo.Postname = DapperDBHelper<PostName>.QueryFirstOrDefault(sql, new { CpId = item.CpId });
                //获取对应的用户信息表信息
                sql = @"SELECT * FROM collectPost
                    INNER JOIN userinfo ON collectPost.`userId` = userinfo.`userId`
                    WHERE cpId = @CpId";
                item.Userinfo = DapperDBHelper<UserInfo>.QueryFirstOrDefault(sql, new { CpId = item.CpId });
            }
            return collectPost;
        }

    }
}
